customreports

Table: customreports

The customreports table stores user-defined queries and reports for data analysis and reporting purposes.
It allows dynamic creation, editing, and execution of custom reports within the system.

---

Columns

Column Name	Data Type	Constraints	Description
queryId	int(11)	NOT NULL, AUTO_INCREMENT	Unique identifier for each custom report
voided	tinyint(4)	NULL	Soft deletion flag (1 = voided, 0 = active)
label	varchar(450)	NULL	Display label for the report
queryName	varchar(450)	NOT NULL	Name of the report/query
description	varchar(450)	NULL	Description of the report purpose
additionalDetails	varchar(450)	NULL	Any additional information about the report
query	varchar(45000)	NULL	The SQL or query definition for the report
dateRange	tinyint(4)	DEFAULT 0	Flag indicating if the report uses a date range filter
locationFilter	tinyint(4)	DEFAULT 0	Flag indicating if the report uses a location filter
createdByUserId	int(11)	NULL	User who created the report
lastEditedByUserId	int(11)	NULL	User who last edited the report
createdDate	datetime	NULL	Timestamp when the report was created
lastEditedDate	datetime	NULL	Timestamp when the report was last modified

---

Indexes

1. PRIMARY - Primary key on queryId
2. user_customReport_idx - Index on createdByUserId
3. lastEdited_user_CustomReport_idx - Index on lastEditedByUserId

---

Foreign Key Relations

1. user_customReport

   • createdByUserId → user.mappedId
   • Tracks the creator of the report

2. lastEdited_user_CustomReport

   • lastEditedByUserId → user.mappedId
   • Tracks the last editor of the report

---

Usage Notes

• Supports dynamic creation and execution of custom reports.
• voided flag allows soft deletion of reports without removing data.
• dateRange and locationFilter define report filtering capabilities.
• Audit fields (createdByUserId, lastEditedByUserId, createdDate, lastEditedDate) maintain history.
• Allows users to store queries up to 45,000 characters in query.